'''
模拟
from .sqlserver_client_sqlalchemy import sql_server_client

sql client based on lib sqlalchemy
'''
import traceback

# python -m pip install SQLAlchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
# from sqlalchemy import create_engine, text
# from sqlalchemy.orm import sessionmaker


class sim_sql_server_client:

    def __init__(self,server = r'192.168.12.89', user = "ass",password = "123", database = 'KittingDB'):
        self.server = server  # 连接服务器地址 ch71w0120  199.63.152.66
        self.username = user  # 连接帐号  MdcsSzPr-rw
        self.password = password # 连接密码 X7tu5#99RT1
        self.database = database # LineKitting

        # self.engine = create_engine(
        #     f'mssql+pyodbc://{self.username}:{self.password}@{self.server}/{database}?driver=ODBC+Driver+17+for+SQL+Server')
        #
        # self.Session = sessionmaker(bind=self.engine)

        self.sim_mes_data = {
            "SELECT * FROM dbo.Kitting_newunittype WHERE F_SKU='CT45-L1N-28D120G'": [{
                'F_Unit_type': 'CT45-L1N-28D120G',
                'F_NeedCheckQCDCS':1 ,
                'F_NeedKitLabel':1,
                'F_BlockStatus':1,

            }],

            "select f_items, f_desc as Items, f_needFai as FAIStatus, F_KeyPart as KeyPartNeedScanSN from Kitting_FAIContains where F_Unit_typeID = (select f_id from kitting_newunittype where f_unit_type='CT45-L1N-28D120G') collate database_default": [
                {'f_items': '50183411-001', 'Items': 'HAND STRAP,BLACK,CT40/CT45', 'FAIStatus': True,
                 'KeyPartNeedScanSN': False},

                {'f_items': 'CT45-L1N-28D120G-M', 'Items': 'CT45,WAN,4,64,FLEX,NANO,GMS,FCC', 'FAIStatus': False,
                 'KeyPartNeedScanSN': False},

                {'f_items': 'CT45XP-ML-RS-06', 'Items': 'REGULATORY SHEET, CT45', 'FAIStatus': True,
                 'KeyPartNeedScanSN': False},

                {'f_items': 'CBL-500-120-S00-05', 'Items': 'USB A/M TO USB TYPE C,120CM', 'FAIStatus': True,
                 'KeyPartNeedScanSN': False},

                {'f_items': '318-055-067', 'Items': 'BATTERY,MAXELL,STD,4020MAH,CT40/CT60', 'FAIStatus': True,
                 'KeyPartNeedScanSN': False},

                # {'f_items': 'EULA-EN-IN-01 Rev A', 'Items': 'Licence', 'FAIStatus': True,
                #  'KeyPartNeedScanSN': False}
            ],

            "select F_Items, F_Desc from Kitting_FAIContains where F_KeyPart='true' and f_unit_typeid=(select f_id from Kitting_NewUnitType where f_unit_type='CT45-L1N-28D120G')":[
            ],

            f"select F_LabelName from Kitting_LabelTemplete where f_id in (select F_LabelID from Kitting_UnitTypePrinter where F_UnitTypeID=(select f_id from kitting_newunittype where F_Unit_type='CT45-L1N-28D120G')) and F_LabelGroupID=(select f_id from Kitting_LabelGroup where F_LabelGroupName='KitLabel') ":[
                {'F_LabelName': '50176073-002_H.POF'}
            ],


            # 包装记录

        }

    def connect(self):

        # self.session = self.Session()
        print(f'sqlserver connection:  {self.server} {self.username} {self.password} {self.database}')

    def select(self, sql_query_str = 'SELECT * FROM dbo.Kitting_newunittype'):

        self.connect()
        print(f'sql_query_str: {sql_query_str}')
        # 执行查询
        # result = self.session.execute(text(sql_query_str))
        result = []
        if self.sim_mes_data.get(sql_query_str):
            result = self.sim_mes_data[sql_query_str] # 模拟
        self.close()

        return result  # [{}, {}]


    def insert(self, record):
        print(f'insert {record}')
        self.connect()
        sn = record['sn']
        self.sim_mes_data[f"select * from Kitting_Product where sn='{sn}'"]= [record] # [{}]
        print(self.sim_mes_data)
        # self.session.add(record)
        # try:
        #     self.session.commit()
        # except Exception as e:
        #     self.session.rollback()
        #     print(f"Error occurred: {e}")
        #     print(traceback.format_exc())
        # finally:
        #     self.session.close()
    def close(self):
        print('sqlserver: close')
        # self.session.close()

    @staticmethod
    def create_inserting_record(sn, unit_type, boxnumber, date, OrderNo, EID):

        kp_record = {
            'sn': sn,
            'unit_type': unit_type,
            'boxnumber': boxnumber,
            'date': date,
            'OrderNo': OrderNo,
            'EID': EID
        }
        return kp_record

def test_sql_server_client():
    # ssclient = sql_server_client(server='ch71w0120', user='MdcsSzPr-rw',
    #                              password='X7tu5#99RT1', database='LineKitting')
    ssclient = sim_sql_server_client(server='199.63.152.27', user='MdcsSzPr-rw',
                                 password='X7tu5#99RT1', database='MDCSPr')

    sql_query_str = "SELECT * FROM dbo.Kitting_newunittype WHERE F_SKU='CT45-L1N-28D120G'"
    # print(sql_query_str)
    row = ssclient.select(sql_query_str) # 查询
    for ro in row:
        print(ro)

    # ssclient.close()

if __name__ =='__main__':
    test_sql_server_client()